package model;

import init.DBManagement;

import java.util.ArrayList;
import java.util.Map;

import model.beans.GradeBean;
import model.beans.StudentGradeBean;

public class Grade {
	
	public static int totalAverage( int int_criterioId, int plan_tk ){
		int int_totalAverage = 0;
		String query_average = "select sum( porcentaje ) from PLACALR where CAL_TECH_KEY <> "+int_criterioId+" and plan_tk = " + plan_tk;
		DBManagement database = new DBManagement();
		database.executeQuery( query_average );
		if( database.getNumRows() > 0 ){
			try{
				int_totalAverage = Integer.parseInt(database.getItem(0, 0));
			}catch(NumberFormatException numberFormatException){
				int_totalAverage = 0;
			}
		}
		return int_totalAverage;
	}

	public static ArrayList list(String str_materia, String academy){
		ArrayList arrL_grades = new ArrayList();
		int size = 0;
		String query_list = "SELECT CAL_TECH_KEY, " +
										 " CRITERIO, " +
										 " PORCENTAJE, " +
										 " PLAN_TK " +
										 " FROM PLACALR cal, " +
										 " PLAMSTB mat " +
										 " WHERE cal.PLAN_TK = mat.PLA_TECH_KEY AND " +
										 " mat.MATERIA_TK = '" +str_materia + "'" + 
										 " ORDER BY 1";
		
		DBManagement database = new DBManagement();
		database.executeQuery( query_list );
		size = database.getNumRows();
		for( int int_i = 0; int_i < size ; int_i++ ){
			GradeBean bn_grade = new GradeBean(Integer.parseInt(database.getItem(int_i, 0)),
																					  database.getItem(int_i, 1),
																					  Integer.parseInt(database.getItem(int_i, 2)),
																					  Integer.parseInt(database.getItem(int_i, 3)), 
																					  academy, 
																					  str_materia);
			arrL_grades.add(bn_grade);
		}		
	 
		return arrL_grades;
	}
	
	public static GradeBean instance( int int_gradeId, String academy, String str_materia){
		GradeBean bn_grade = null;
		String query_inst = "SELECT CRITERIO, " +
										 " PORCENTAJE, " +
										 " PLAN_TK " +										
										 " FROM PLACALR " +
										 " WHERE CAL_TECH_KEY=" + int_gradeId;
		
		DBManagement database = new DBManagement();
		database.executeQuery( query_inst );
		bn_grade = new GradeBean(int_gradeId,
														  database.getItem(0, 0), 
														  Integer.parseInt(database.getItem(0, 1)), 
														  Integer.parseInt(database.getItem(0, 2)),
														  academy,
														  str_materia);
		database = null;
		return bn_grade;
	}
	
	public static int add( Map map_data, int plan_tk){
		int int_add;		   
		String str_add =
            " INSERT INTO PLACALR ( "+
            "        CRITERIO, "+
            "        PORCENTAJE, "+
            "        PLAN_TK "+
            " ) VALUES ( "+
            "        '"+(String)map_data.get("str_criterio")+"', "+
            "        '"+(Integer)map_data.get("int_porcentaje")+"', "+
            "        "+plan_tk+" "+
            " ) ";
		DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_add );
        int_add = bol_insertStatus ? 1 : 0;
        database = null;
	    return int_add;
	}
	
	public static int edit(Map map_data){
		int int_edit;
		Integer int_gradeId = (Integer)map_data.get("int_gradeId");
		String str_criterio = (String)map_data.get("str_criterio");
		Integer int_porcentaje = (Integer)map_data.get("int_porcentaje");
		String str_edit =
			 " UPDATE PLACALR "+
	            " SET    CRITERIO = '"+str_criterio+"', "+
	            "        PORCENTAJE =  "+int_porcentaje+	         
	            " where  CAL_TECH_KEY = "+int_gradeId;
		
		DBManagement database = new DBManagement();
        boolean bol_insertStatus = database.executeQuery( str_edit );
        int_edit = bol_insertStatus ? 1 : 0;
        database = null;
	    return int_edit;
	}
	
	public static int delete( int int_gradeId ){
		int int_retVal = 0;
		String str_delete = 
			" DELETE FROM PLACALR "+
			" WHERE CAL_TECH_KEY = "+int_gradeId;
		DBManagement db = null;
		try{
			db = new DBManagement();
			db.executeQuery(str_delete);
			int_retVal = 1;
		}catch(Exception ex){
		}finally{
			db = null;
		}
		
		return int_retVal;
	}
	
	public static ArrayList studentGradeList(String str_alumnoId, String str_ciclo, String str_crn){
		ArrayList arrL_grades = new ArrayList();
		int size = 0;
		String temp = null;
		StudentGradeBean bn_student_grade = null;
		String query_list = "SELECT cal.CAA_TECH_KEY, " +
										 " cal.CALIFICACION, " +
										 " crit.CRITERIO, " +
										 " crit.CAL_TECH_KEY " +										 										
										 " FROM PLACAAR cal " +
										 " RIGHT JOIN PLACALR crit "+
										 " ON cal.CRITERIO_TK=crit.CAL_TECH_KEY " +
										 " AND cal.ALUMNO_TK = '" + str_alumnoId + "'" +
										 " AND cal.CICLO = '" + str_ciclo +"'" +
										 " AND cal.CRN = '" + str_crn + "'";	
		
		DBManagement database = new DBManagement();
		database.executeQuery( query_list );
		size = database.getNumRows();
		for( int int_i = 0; int_i < size ; int_i++ ){
			bn_student_grade = new StudentGradeBean();
				if((temp=database.getItem(int_i, 0))!=null)
					bn_student_grade.setInt_calId(Integer.parseInt(temp));
				
				if((temp=database.getItem(int_i, 1))!=null)
					bn_student_grade.setInt_calificacion(Integer.parseInt(temp));
				
				bn_student_grade.setStr_alumnoId(str_alumnoId);
				bn_student_grade.setStr_criterio( database.getItem(int_i, 2));
				bn_student_grade.setStr_ciclo(str_ciclo);
				bn_student_grade.setStr_crn(str_crn);
				bn_student_grade.setInt_criterioId(Integer.parseInt(database.getItem(int_i, 3)));
																								  
			arrL_grades.add(bn_student_grade);
		}		
		
		return arrL_grades;
	}
	
	public static int studentGradeSave(Map map_data, String str_alumnoId, String str_ciclo, String str_crn){
		int ret_val = 0;
		boolean querySuccess = false;
		int size = map_data.size()/3;
		int int_calId = 0;
		DBManagement database = new DBManagement();
		
		for(int i=0; i<size; i++){
			int_calId =((Integer)map_data.get("int_calId" + i)).intValue();
			if(int_calId==0){
				querySuccess = database.executeQuery( 
						" INSERT INTO PLACAAR( " +
						" CALIFICACION, " +
						" ALUMNO_TK, " +
						" CRITERIO_TK, " +
						" CICLO, " +
						" CRN) VALUES( " +
						((Integer)map_data.get("int_calificacion" + i)).intValue() + ", " +
						" '" + str_alumnoId + "', " +
						((Integer)map_data.get("int_criterioId" + i)).intValue() + ", " +
						" '" + str_ciclo + "', " +
						" '" + str_crn + "')");
			}else{
				querySuccess = database.executeQuery(
						" UPDATE PLACAAR " +
						 " SET CALIFICACION = " + ((Integer)map_data.get("int_calificacion" + i)).intValue() +
						 " WHERE CAA_TECH_KEY =" +int_calId);
			}
		}
		if(querySuccess){
			ret_val = 1;
		}
		return ret_val;
	}
}
